﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spWH_Perfmon]
#-- Purpose:		Copies the perfmon activity to the Warehouse
#--	Last Update:	03/21/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spWH_Perfmon]
AS

--- Declare Local Variables
DECLARE	@sSQL varchar(MAX)

IF EXISTS (SELECT TOP 1 * FROM sys.servers WHERE name = '$(MSXServer)')
  BEGIN
	SELECT	@sSQL = 'DECLARE @mnt_server_ssd_id int, @max_date datetime

SELECT	@mnt_server_ssd_id = [ssd_id]
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
WHERE	[ssd_sqlserver_name_ro] = @@SERVERNAME

SELECT	@max_date = MAX(date_inserted)
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Perfmon]
WHERE	[server_ssd_id] = @mnt_server_ssd_id

IF @mnt_server_ssd_id IS NOT NULL
  BEGIN
	INSERT INTO [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Perfmon]
		(
		[server_ssd_id],
		[date_inserted],
		[buffer_cache_hit_ratio],
		[page_life_expectancy],
		[logins_per_sec],
		[logouts_per_sec],
		[user_connections],
		[optimizer_memory_KB],
		[sql_cache_memory_KB],
		[target_server_memory_KB],
		[total_server_memory_KB],
		[sql_compilations_per_sec],
		[sql_recompilations_per_sec],
		[batch_requests_per_sec],
		[number_of_deadlocks_per_sec],
		[average_wait_time_ms],
		[lock_waits_per_sec],
		[lock_wait_time_ms],
		[full_scans_per_sec],
		[lazy_writes_per_sec],
		[page_reads_per_sec],
		[page_writes_per_sec],
		[checkpoint_pages_per_sec],
		[page_splits_per_sec],
		[transactions_per_sec],
		[cpu_busy],
		[io_busy],
		[idle]
		)
	SELECT		@mnt_server_ssd_id,
				[date_inserted],
				[buffer_cache_hit_ratio],
				[page_life_expectancy],
				[logins_per_sec],
				[logouts_per_sec],
				[user_connections],
				[optimizer_memory_KB],
				[sql_cache_memory_KB],
				[target_server_memory_KB],
				[total_server_memory_KB],
				[sql_compilations_per_sec],
				[sql_recompilations_per_sec],
				[batch_requests_per_sec],
				[number_of_deadlocks_per_sec],
				[average_wait_time_ms],
				[lock_waits_per_sec],
				[lock_wait_time_ms],
				[full_scans_per_sec],
				[lazy_writes_per_sec],
				[page_reads_per_sec],
				[page_writes_per_sec],
				[checkpoint_pages_per_sec],
				[page_splits_per_sec],
				[transactions_per_sec],
				[cpu_busy],
				[io_busy],
				[idle]
	FROM		[dbo].[tblMonitor_Perfmon]
	WHERE		date_inserted > @max_date
				OR @max_date IS NULL
	ORDER BY	date_inserted
  END
ELSE
	RAISERROR(''This server has not been registered with the MSX Server'', 16, 1)'
	
	EXEC	(@sSQL)
  END